package com.youan.log.modules.audit.dataanalysis;

import com.youan.log.common.threadtask.ThreadTaskProgressor;
import com.youan.log.modules.audit.entity.SdAttackIpDetail;
import com.youan.log.modules.audit.service.ISdAttackIpDetailService;
import org.springblade.core.tool.utils.SpringUtil;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;

/**
 * 去重统计被攻击IP，记录首次被攻击时间和最新被攻击时间，精确到秒（原始数据已提供）记录IP归属国、省、市，后续将提供接口，定位到人。结果单独存入一张表，累加存储，
 */
public class SdAttackIpDetailDataExtract {

    private String date;
    private ThreadTaskProgressor taskProgressor;
    private ISdAttackIpDetailService sdAttackIpDetailService;
    private JdbcTemplate jdbcTemplate;
    private List<SdAttackIpDetail> needAddToDB = new ArrayList<>();
    private int BATCH_SIZE = 5000;

    public SdAttackIpDetailDataExtract(String date, ThreadTaskProgressor taskProgressor) {
        this.date = date;
        this.taskProgressor = taskProgressor;
        DataSource dataSource = SpringUtil.getBean(DataSource.class);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        sdAttackIpDetailService = SpringUtil.getBean(ISdAttackIpDetailService.class);
        taskProgressor.updateProgressTotal(taskProgressor.getProgressTotalNum() + getCount());

    }

    public void extract() {
        String sql = "SELECT DISTINCT\n" +
                "   attacker_ip,\n" +
                "   city attackerCity, \n" +
                "   country attackerCountry, \n" +
                "   province attackerProvince, \n" +
                "   0 attackSuccessNum \n" +
                "FROM\n" +
                "   log_attack_analysis a\n" +
                "where date = '" + this.date + "'\n" +
                "and not exists (select * from log_sd_attack_ip_detail b where a.attacker_ip = b.attacker_ip)";
        jdbcTemplate.query(sql, rs-> {

            SdAttackIpDetail item = new SdAttackIpDetail();
            item.setAttackerIp(rs.getString("attacker_ip"));
            item.setAttackerCity(rs.getString("attackerCity"));
            item.setAttackerCountry(rs.getString("attackerCountry"));
            item.setAttackerProvince(rs.getString("attackerProvince"));
            item.setAttackSuccessNum(rs.getInt("attackSuccessNum"));

            needAddToDB.add(item);

            if(needAddToDB.size() >= BATCH_SIZE) {
                sdAttackIpDetailService.saveBatch(needAddToDB);
                taskProgressor.addProgressNum(needAddToDB.size());
                needAddToDB = new ArrayList<>();
            }
        });
        sdAttackIpDetailService.saveBatch(needAddToDB);
        taskProgressor.addProgressNum(needAddToDB.size());

        // 最终总体从数据库里批量更新，不在一个一个的查找更新，效率太低
        String updateStr = "UPDATE log_sd_attack_ip_detail detail\n" +
                "SET detail.first_attack_time = IFNULL(\n" +
                "  (\n" +
                "    SELECT\n" +
                "      min(a.first_occurrence_time)\n" +
                "    FROM\n" +
                "      log_statistical a\n" +
                "    WHERE\n" +
                "      a.source_ip = detail.attacker_ip\n" +
                "    AND visit_date = '" + this.date + "'\n" +
                "  ),\n" +
                "  '2999-10-10'\n" +
                ")\n" +
                "WHERE\n" +
                "  detail.first_attack_time IS NULL";

        jdbcTemplate.execute(updateStr);

        updateStr = "UPDATE log_sd_attack_ip_detail detail\n" +
                "SET detail.first_attack_time = LEAST(\n" +
                "  IFNULL(\n" +
                "    (\n" +
                "      SELECT\n" +
                "        MIN(a.first_occurrence_time)\n" +
                "      FROM\n" +
                "        log_statistical a\n" +
                "      WHERE\n" +
                "        a.dest_ip = detail.attacker_ip\n" +
                "      AND visit_date = '" + this.date + "'\n" +
                "    ),\n" +
                "    '2999-10-10'\n" +
                "  ),\n" +
                "  detail.first_attack_time\n" +
                ")\n" +
                "WHERE\n" +
                "  SUBSTRING(first_attack_time FROM 1 FOR 10) >= '" + this.date + "'";

        jdbcTemplate.execute(updateStr);

        updateStr = "UPDATE log_sd_attack_ip_detail detail\n" +
                "SET detail.latest_attack_time = (\n" +
                "  IFNULL(\n" +
                "    (\n" +
                "      SELECT\n" +
                "        MAX(a.latest_occurrence_time)\n" +
                "      FROM\n" +
                "        log_statistical a\n" +
                "      WHERE\n" +
                "        a.source_ip = detail.attacker_ip\n" +
                "      \n" +
                "      AND visit_date = '" + this.date + "'\n" +
                "    ),\n" +
                "    '0000-00-00'\n" +
                "  )\n" +
                ")\n" +
                "WHERE\n" +
                "  detail.latest_attack_time IS NULL";

        jdbcTemplate.execute(updateStr);

        updateStr = "UPDATE log_sd_attack_ip_detail detail\n" +
                "SET detail.latest_attack_time = GREATEST(\n" +
                "  IFNULL(\n" +
                "    (\n" +
                "      SELECT\n" +
                "        MAX(a.latest_occurrence_time)\n" +
                "      FROM\n" +
                "        log_statistical a\n" +
                "      WHERE\n" +
                "        a.dest_ip = detail.attacker_ip\n" +
                "      AND visit_date = '" + this.date + "'\n" +
                "    ),\n" +
                "    '0000-00-00'\n" +
                "  ),\n" +
                "  detail.latest_attack_time\n" +
                ")\n" +
                "WHERE\n" +
                "  SUBSTRING(latest_attack_time FROM 1 FOR 10) <= '" + this.date + "'";

        jdbcTemplate.execute(updateStr);

        // 更新攻击成功数
        String updateAttackSuccessSql = "UPDATE log_sd_attack_ip_detail a\n" +
                "SET attack_success_num = IFNULL((\n" +
                " SELECT\n" +
                "  b.attack_success_count\n" +
                " FROM\n" +
                "  log_attack_analysis b\n" +
                " WHERE\n" +
                "  a.attacker_ip = b.attacker_ip\n" +
                " AND date = '" + this.date + "'\n" +
                "), 0) + attack_success_num";
        jdbcTemplate.execute(updateAttackSuccessSql);
    }

    private int getCount() {
        String sql = "SELECT\n" +
                " count(1)\n" +
                "FROM\n" +
                "   log_attack_analysis\n" +
                "where date = '" + this.date + "'";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }
}
